package cn.com.cifi.mars.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.concurrent.atomic.AtomicInteger;

import cn.com.cifi.mars.constant.Constants;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.com.cifi.mars.bean.vo.supply.BaseExcelValueVO;
import cn.com.cifi.mars.bean.vo.supply.MonthExcelValueVO;
import cn.com.cifi.mars.bean.vo.supply.SupplyPlanExcelVO;
import cn.com.cifi.mars.bean.vo.supply.YearExcelValueVO;
import cn.com.cifi.mars.constant.OrgTypeEunm;

public class OverallSupplyReportTemplate {
    
        private static final String TEMPLATE_PATH = "/template/overall_supply_template.xlsx";

	    private XSSFWorkbook workbook;

	    private XSSFSheet sheet;

	    /**
	     * 默认sheet索引
	     */
	    private int defaultSheetIndex = 0;

	    /**
	     * 数据开始行
	     */
	    private int dataBeginRow = 3;

	    /**
	     * 当前行标
	     */
	    private int currentDataRowCursor = dataBeginRow;

	    /**
	     * 月份动态列模板开始索引
	     */
	    private int dynamicColumnBeginIndex = 27;

	    /**
	     * 月份动态列模板结束索引
	     */
	    private int dynamicColumnEndIndex = 36;
	    
	    private int baseIndex = 18;
	    
	    //单元格样式
	    private XSSFCellStyle headCellStyle;
	    
	    
	    /**
	     * 常规样式-不带底色
	     */
	    private XSSFCellStyle cellStyle1;
	    /**
	     * 常规样式-带底色
	     */
	    private XSSFCellStyle cellStyle2;
	    
	    /**
	     * 样式（数字格式-0位小数，居右）-不带底色
	     */
	    private XSSFCellStyle cellStyle3;
	    /**
	     * 样式（数字格式-0位小数，居右）-带底色
	     */
	    private XSSFCellStyle cellStyle4;
	    
	    /**
	     * 样式（数字格式-2位小数，居右）-不带底色
	     */
	    private XSSFCellStyle cellStyle5;
	    
	    /**
	     * 样式（数字格式-2位小数，居右）-带底色
	     */
	    private XSSFCellStyle cellStyle6;
	    
	    /**
	     * 样式（百分比格式-1位小数，居右）-不带底色
	     */
	    private XSSFCellStyle cellStyle7;
	    /**
	     * 样式（数字格式-1位小数，居右）-带底色
	     */
	    private XSSFCellStyle cellStyle8;
	    
	    /**
	     * 样式（百分比格式-2位小数，居右）-不带底色
	     */
	    private XSSFCellStyle cellStyle9;
	    /**
	     * 样式（百分比格式-2位小数，居右）-带底色
	     */
	    private XSSFCellStyle cellStyle10;
	    
	    /**
	     * 权益比例
	     */
	    private static final String EQUITY_RATIO  = "equityRatio";
	    /**
	     * 数字-两位小数
	     */
		private static final String NUMERIC_TWO  = "numericByTwo";
		/**
	     * 数字-0位小数
	     */
		private static final String NUMERIC_ZERO  = "numericByZero";
		/**
	     * 达成率
	     */
		private static final String ACHIEVING_RATE   = "achievingRate ";
	    
	    
	    public OverallSupplyReportTemplate(LocalDateTime date,Set<String> yearSet,String projectName) {
	        try {
	            InputStream tempInputStream = OverallSupplyReportTemplate.class.getResourceAsStream(TEMPLATE_PATH);
	            if (tempInputStream != null) {
	                workbook = new XSSFWorkbook(tempInputStream);
	                sheet = workbook.getSheetAt(defaultSheetIndex);
	                XSSFRow row = sheet.getRow(defaultSheetIndex);
	                headCellStyle = row.getCell(dynamicColumnBeginIndex).getCellStyle();
	                setSheetDate(date,projectName);
	                //固定列数（月份12+年度合计1+下一年复制使用1）
	                int columnNum = 14;
	                boolean flag = false;
	                List<String> years = new ArrayList<>(yearSet);
	                for(int i = 0; i< years.size() ;i++) {
	                	//判断是否是最后 一年
	                	if(i == years.size()-1) {
	                		flag = true;
	                		columnNum = columnNum-1;
	                	}
	                	insertDynamicColumn(Integer.valueOf(years.get(i)),columnNum,flag);
	                	dynamicColumnBeginIndex = dynamicColumnBeginIndex+((columnNum-1)*9);
	                	dynamicColumnEndIndex = dynamicColumnBeginIndex +9;
	                }
	                //常规
	                cellStyle1=sheet.getRow(dataBeginRow).getCell(0).getCellStyle();
	                cellStyle2=sheet.getRow(dataBeginRow+1).getCell(0).getCellStyle();
	                //数字格式-0位小数
	                cellStyle3=sheet.getRow(dataBeginRow).getCell(18).getCellStyle();
	                cellStyle4=sheet.getRow(dataBeginRow+1).getCell(18).getCellStyle();
	                //数字格式-2位小数
	                cellStyle5=sheet.getRow(dataBeginRow).getCell(19).getCellStyle();
	                cellStyle6=sheet.getRow(dataBeginRow+1).getCell(19).getCellStyle();
	                //百分比格式-1位小数
	                cellStyle7=sheet.getRow(dataBeginRow).getCell(26).getCellStyle();
	                cellStyle8=sheet.getRow(dataBeginRow+1).getCell(26).getCellStyle();
	                //百分比格式-2位小数
	                cellStyle9=sheet.getRow(dataBeginRow).getCell(4).getCellStyle();
	                cellStyle10=sheet.getRow(dataBeginRow+1).getCell(4).getCellStyle();
	                
	            }
	        } catch (Exception e) {
	            throw new RuntimeException(e);
	        }
	    }
	    
	    public void setSheetDate(LocalDateTime date,String projectName) {
	        workbook.setSheetName(defaultSheetIndex, workbook.getSheetName(defaultSheetIndex).replace("供货计划", StrUtils.replaceStr(projectName)+"最新全盘供货计划-".concat(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHH")))));
	    }
	    
		public void insertDynamicColumn(Integer year,int columnNum,boolean flag) {
    		int dynamicLen = dynamicColumnEndIndex - dynamicColumnBeginIndex;
	        for (int i = 0; i < this.dataBeginRow; i++) {
	        	 XSSFRow row = sheet.getRow(i);
	            for (int k = 1; k <= columnNum; k++) {
	                for (int j = dynamicColumnBeginIndex; j < dynamicColumnEndIndex; j++) {
	                    int sum = dynamicLen * (k - 1);
	                    XSSFCell to = row.createCell(j + sum);
	                    to.setCellStyle(headCellStyle);
	                    if(i == 0) {
                    		if(flag) {
                    			if(k==columnNum) {
	                    			to.setCellValue(year + "年度合计");
	                    		}else {
	                    			to.setCellValue(year + "年" + (k < 10 ? "0" + k : k) + "月");
	                    		}
                    		}else {
                    			if(k==columnNum-1) {
	                    			to.setCellValue(year + "年度合计");
	                    		}else {
	                    			to.setCellValue(year + "年" + (k < 10 ? "0" + k : k) + "月");
	                    		}
                    		}
                    	}else {
                    		String columnValue = "";
                    		if(i == 1) {
                    			if(j >= dynamicColumnBeginIndex && j<= dynamicColumnBeginIndex+3) {
                    				columnValue = "供货计划";
                    			}else if(j >= dynamicColumnBeginIndex+4 && j<= dynamicColumnBeginIndex+4+3) {
                    				columnValue = "供货实际";
                    			}else if(j == dynamicColumnBeginIndex+8){
                    				columnValue = "达成率";
                    			}
                    		}else if(i == 2) {
                    			if( j == dynamicColumnBeginIndex || j == dynamicColumnBeginIndex+4) {
                    				columnValue = "套数 /车位数（套/个）";
                    			}else if( j == dynamicColumnBeginIndex+1 || j == dynamicColumnBeginIndex+5) {
                    				columnValue = "面积（平米）";
                    			}else if( j == dynamicColumnBeginIndex+2 || j == dynamicColumnBeginIndex+6) {
                    				columnValue = "均价（元/平米,元/个）";
                    			}else if( j == dynamicColumnBeginIndex+3 || j == dynamicColumnBeginIndex+7) {
                    				columnValue = "金额（万元）";
                    			}
                    		}
                    		to.setCellValue(columnValue);
                    	}
	                    
	                    if (i == 0 && j == dynamicColumnEndIndex - 1 && k != 1) {
	                        sheet.addMergedRegion(new CellRangeAddress(i, i, j + 1 + sum - dynamicLen, j + sum));
	                    } else if (i == 1 && j == dynamicColumnEndIndex - 1 && k != 1) {
	                        sheet.addMergedRegion(new CellRangeAddress(i, i, j + 1 + sum - dynamicLen, j + sum - dynamicLen + 4));
	                        sheet.addMergedRegion(new CellRangeAddress(i, i, j + 1 + sum - dynamicLen + 4, j + sum - dynamicLen + 4 + 4));
	                        sheet.addMergedRegion(new CellRangeAddress(i, i+1, j + 1 + sum - dynamicLen + 4 + 4, j + sum - dynamicLen + 4 + 4 + 1));
	                    }
	                }
	            }
	        }
	    }
		
		public void copyRowStyle(XSSFRow from, XSSFRow to) {
	        if (from != null && to != null && from != to && from.getRowStyle() != null) {
	            to.setRowStyle(from.getRowStyle());
	            to.setHeight(from.getHeight());
	        }
	    }

	    public void copyCellStyle(XSSFCell from, XSSFCell to) {
	        if (from != null && to != null && from != to && from.getCellStyle() != null) {
	            to.setCellStyle(from.getCellStyle());
	        }
	    }
	    
	    /**
	     * 插入列
	     *
	     * @param row
	     * @param index
	     * @param value
	     */
	    public synchronized void appendCell(XSSFRow row, int index, String value,boolean isParentLevel,String type) {
	    	XSSFCell cell = row.getCell(index);
	        if (cell == null) {
	            cell = row.createCell(index);
	        }
	        if(index >= baseIndex) {
	        	String cellValue =  sheet.getRow(2).getCell(index).getStringCellValue();
	        	if(cellValue.equals("套数 /车位数（套/个）") || cellValue.equals("面积（平米）") || cellValue.equals("均价（元/平米,元/个）")) {
	        		sheet.setColumnWidth(index, 26*256);
	        	}else if(cellValue.equals("金额（万元）")){
	        		sheet.setColumnWidth(index, 36*256);
	        	}
	        	if(isParentLevel) {
	        		switch (type) {
					case NUMERIC_ZERO:
						cell.setCellStyle(cellStyle4);
						if(StrUtils.isNotEmpty(value) && value.indexOf("/") <= 0) {
							cell.setCellValue(Double.valueOf(value.replace(",", "")).doubleValue());
						}else {
							cell.setCellValue(value);
						}
						break;
					case NUMERIC_TWO:
						cell.setCellStyle(cellStyle6);
						if(StrUtils.isNotEmpty(value) && value.indexOf("/") <= 0) {
							cell.setCellValue(Double.valueOf(value.replace(",", "")).doubleValue());
						}else {
							cell.setCellValue(value);
						}
						break;
					case ACHIEVING_RATE:
						cell.setCellStyle(cellStyle8);
						if(StrUtils.isNotEmpty(value) && value.equals("-")) {
	        				cell.setCellValue("-");
	        			}else if(StrUtils.isNotEmpty(value)) {
	        				cell.setCellValue(Double.valueOf(value).doubleValue());
	        			}else {
		                	cell.setCellValue(value);
	        			}
						break;
					default:
						cell.setCellStyle(cellStyle2);
						cell.setCellValue(value);
						break;
	        		}
	        	}else {
	        		switch (type) {
					case NUMERIC_ZERO:
						cell.setCellStyle(cellStyle3);
						if(StrUtils.isNotEmpty(value) && value.indexOf("/") <= 0) {
							cell.setCellValue(Double.valueOf(value.replace(",", "")).doubleValue());
						}else {
							cell.setCellValue(value);
						}
						break;
					case NUMERIC_TWO:
						cell.setCellStyle(cellStyle5);
						if(StrUtils.isNotEmpty(value) && value.indexOf("/") <= 0) {
							cell.setCellValue(Double.valueOf(value.replace(",", "")).doubleValue());
						}else {
							cell.setCellValue(value);
						}
						break;
					case ACHIEVING_RATE:
						cell.setCellStyle(cellStyle7);
						if(StrUtils.isNotEmpty(value) && value.equals("-")) {
	        				cell.setCellValue("-");
	        			}else if(StrUtils.isNotEmpty(value)) {
	        				cell.setCellValue(Double.valueOf(value).doubleValue());
	        			}else {
		                	cell.setCellValue(value);
	        			}
						break;
					default:
						cell.setCellStyle(cellStyle1);
						cell.setCellValue(value);
						break;
	        		}
	        	}
	        }else {
	        	if(isParentLevel) {
	        		switch (type) {
					case NUMERIC_ZERO:
						cell.setCellStyle(cellStyle4);
						if(StrUtils.isNotEmpty(value) && value.indexOf("/") <= 0) {
							cell.setCellValue(Double.valueOf(value).doubleValue());
						}else {
							cell.setCellValue(value);
						}
						break;
					case NUMERIC_TWO:
						cell.setCellStyle(cellStyle6);
						if(StrUtils.isNotEmpty(value) && value.indexOf("/") <= 0) {
							cell.setCellValue(Double.valueOf(value).doubleValue());
						}else {
							cell.setCellValue(value);
						}
						break;
					case ACHIEVING_RATE:
						cell.setCellStyle(cellStyle8);
						if(StrUtils.isNotEmpty(value) && value.equals("-")) {
	        				cell.setCellValue("-");
	        			}else if(StrUtils.isNotEmpty(value)) {
	        				cell.setCellValue(Double.valueOf(value).doubleValue());
	        			}else {
		                	cell.setCellValue("");
	        			}
						break;
				    case EQUITY_RATIO:
						cell.setCellStyle(cellStyle10);
						if(StrUtils.isNotEmpty(value)) {
							double dValue = Double.valueOf(NumberUtils.divide(value, "100", 4)).doubleValue();
							cell.setCellValue(dValue);
	        			}else {
		                	cell.setCellValue(value);
	        			}
						break;
					default:
						cell.setCellStyle(cellStyle2);
						cell.setCellValue(value);
						break;
					}
	        	}else {
	        		switch (type) {
					case NUMERIC_ZERO:
						cell.setCellStyle(cellStyle3);
						if(StrUtils.isNotEmpty(value) && value.indexOf("/") <= 0) {
							cell.setCellValue(Double.valueOf(value).doubleValue());
						}else {
							cell.setCellValue(value);
						}
						break;
					case NUMERIC_TWO:
						cell.setCellStyle(cellStyle5);
						if(StrUtils.isNotEmpty(value) && value.indexOf("/") <= 0) {
							cell.setCellValue(Double.valueOf(value).doubleValue());
						}else {
							cell.setCellValue(value);
						}
						break;
					case ACHIEVING_RATE:
						cell.setCellStyle(cellStyle7);
						if(StrUtils.isNotEmpty(value) && value.equals("-")) {
	        				cell.setCellValue("-");
	        			}else if(StrUtils.isNotEmpty(value)) {
	        				cell.setCellValue(Double.valueOf(value).doubleValue());
	        			}else {
		                	cell.setCellValue("");
	        			}
						break;
				    case EQUITY_RATIO:
						cell.setCellStyle(cellStyle9);
						if(StrUtils.isNotEmpty(value)) {
							double dValue = Double.valueOf(NumberUtils.divide(value, "100", 4)).doubleValue();
							cell.setCellValue(dValue);
	        			}else {
		                	cell.setCellValue(value);
	        			}
						break;
					default:
						cell.setCellStyle(cellStyle1);
						cell.setCellValue(value);
						break;
					}
	        	}
	        }
	    }
	    
	    private double getDouble(String value) {
	    	BigDecimal bigDecimal = MathUtils.newBigDecimal(value.replace("%", ""));
            return bigDecimal.divide(MathUtils.NUM_100,bigDecimal.scale()+2, RoundingMode.HALF_UP).doubleValue();
	    }
	    
	    private double getAreaDouble(String value) {
	    	if(value.indexOf(",") > 0) {
	    		return Double.valueOf(value.replace(",", ""));
	    	}else {
	    		return Double.valueOf(value);
	    	}
	    }
	    
	    /**
	     * 批量插入行
	     *
	     * @param list
	     */
	    public synchronized void appendRows(List<SupplyPlanExcelVO> list) {
	    	for (SupplyPlanExcelVO vo : list) {
	    		boolean isParentLevel = vo.getType().equals(OrgTypeEunm.ORG_BUILD.getKey()) ? false : true;
	            this.appendRow(vo,isParentLevel);
	        }
	    }

		/**
	     * 插入行
	     *
	     * @param map
	     */
	    public synchronized void appendRow(SupplyPlanExcelVO vo,boolean isParentLevel) {
	    	XSSFRow row = sheet.getRow(currentDataRowCursor);
	        if (row == null) {
	            row = sheet.createRow(currentDataRowCursor);
	        }
	        row.setHeightInPoints(Constants.HEIGHT_POINTS);
	        AtomicInteger index = new AtomicInteger(-1);
	        //项目信息
	        this.appendCell(row, index.addAndGet(1),vo.getProjectName(),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),vo.getProjectCode(),isParentLevel,"");
	        //分期信息
	        this.appendCell(row, index.addAndGet(1),vo.getStageName(),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),vo.getStageCode(),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),vo.getEquityRatio(),isParentLevel,EQUITY_RATIO);
	        this.appendCell(row, index.addAndGet(1),vo.getTradersWay(),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),handleIsMergeTable(vo.getIsMergeTable()),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),handlePendingHomeSale(vo.getPendingHomeSale()),isParentLevel,"");
	        //组团信息
	        this.appendCell(row, index.addAndGet(1),vo.getGroupName(),isParentLevel,"");
	        //产品信息
	        this.appendCell(row, index.addAndGet(1),vo.getProductTypeName(),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),vo.getProductTypeCode(),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),vo.getProductName(),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),vo.getProductCode(),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),vo.getBulidNo(),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),handlePendingHomeSale(vo.getPendingHomeSale2()),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),vo.getBusinessTypeName(),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),vo.getFreeTypeName(),isParentLevel,"");
	        this.appendCell(row, index.addAndGet(1),vo.getProTypeName(),isParentLevel,"");
	        //全盘供货计划与实际
	        String type = vo.getType();
	        Integer isParking = vo.getIsParking();
	        if(type.equals(OrgTypeEunm.ORG_BUILD.getKey()) || type.equals(OrgTypeEunm.ORG_PRODUCT.getKey())) {
	        	this.appendCell(row, index.addAndGet(1),getNum(isParking,vo.getOverAllValueVO().getNum()),isParentLevel,NUMERIC_ZERO);
	 	        this.appendCell(row, index.addAndGet(1),getArea(isParking,vo.getOverAllValueVO().getSaleArea()),isParentLevel,NUMERIC_TWO);
	 	        this.appendCell(row, index.addAndGet(1),getAvgPrice(isParking,vo.getOverAllValueVO().getAvgPrice()),isParentLevel,NUMERIC_ZERO);
	 	        this.appendCell(row, index.addAndGet(1),getPrice(isParking,vo.getOverAllValueVO().getPrice()),isParentLevel,NUMERIC_ZERO);
	 	        this.appendCell(row, index.addAndGet(1),getNum(isParking,vo.getOverAllValueVO().getActualNum()),isParentLevel,NUMERIC_ZERO);
	 	        this.appendCell(row, index.addAndGet(1),getArea(isParking,vo.getOverAllValueVO().getActualArea()),isParentLevel,NUMERIC_TWO);
	 	        this.appendCell(row, index.addAndGet(1),getAvgPrice(isParking,vo.getOverAllValueVO().getActualAveragePrice()),isParentLevel,NUMERIC_ZERO);
	 	        this.appendCell(row, index.addAndGet(1),getPrice(isParking,vo.getOverAllValueVO().getActualPrice()),isParentLevel,NUMERIC_ZERO);
	 	        this.appendCell(row, index.addAndGet(1),vo.getOverAllValueVO().getAchievingRate(),isParentLevel,ACHIEVING_RATE);
	        }else {
	        	this.appendCell(row, index.addAndGet(1),handleNum(vo.getOverAllValueVO().getNum()),isParentLevel,NUMERIC_ZERO);
	 	        this.appendCell(row, index.addAndGet(1),getArea(isParking,vo.getOverAllValueVO().getSaleArea()),isParentLevel,NUMERIC_TWO);
	 	        this.appendCell(row, index.addAndGet(1),vo.getOverAllValueVO().getAvgPrice(),isParentLevel,NUMERIC_ZERO);
	 	        this.appendCell(row, index.addAndGet(1),vo.getOverAllValueVO().getPrice(),isParentLevel,NUMERIC_ZERO);
	 	        this.appendCell(row, index.addAndGet(1),handleNum(vo.getOverAllValueVO().getActualNum()),isParentLevel,NUMERIC_ZERO);
	 	        this.appendCell(row, index.addAndGet(1),getArea(isParking,vo.getOverAllValueVO().getActualArea()),isParentLevel,NUMERIC_TWO);
	 	        this.appendCell(row, index.addAndGet(1),vo.getOverAllValueVO().getActualAveragePrice(),isParentLevel,NUMERIC_ZERO);
	 	        this.appendCell(row, index.addAndGet(1),vo.getOverAllValueVO().getActualPrice(),isParentLevel,NUMERIC_ZERO);
	 	        this.appendCell(row, index.addAndGet(1),vo.getOverAllValueVO().getAchievingRate(),isParentLevel,ACHIEVING_RATE);
	        }
	        
	        //年度每月合计
	        List<YearExcelValueVO> yearVo = vo.getYearExcelValueVO();
	        if(CollectionUtils.isNotEmpty(yearVo)) {
	        	for(int i = 0 ; i < yearVo.size() ; i++) {
		        	//月度合计
		        	List<MonthExcelValueVO> monthValueVo = yearVo.get(i).getMonthValueVO();
		        	if(CollectionUtils.isNotEmpty(monthValueVo)) {
		        		for(int j = 0 ;j< monthValueVo.size(); j++) {
		        			if(type.equals(OrgTypeEunm.ORG_BUILD.getKey()) || type.equals(OrgTypeEunm.ORG_PRODUCT.getKey())) {
		        				this.appendCell(row, index.addAndGet(1),getNum(isParking,monthValueVo.get(j).getNum()),isParentLevel,NUMERIC_ZERO);
				 		        this.appendCell(row, index.addAndGet(1),getArea(isParking,monthValueVo.get(j).getSaleArea()),isParentLevel,NUMERIC_TWO);
				 		        this.appendCell(row, index.addAndGet(1),getAvgPrice(isParking,monthValueVo.get(j).getAvgPrice()),isParentLevel,NUMERIC_ZERO);
				 		        this.appendCell(row, index.addAndGet(1),getPrice(isParking,monthValueVo.get(j).getPrice()),isParentLevel,NUMERIC_ZERO);
				 		        this.appendCell(row, index.addAndGet(1),getNum(isParking,monthValueVo.get(j).getActualNum()),isParentLevel,NUMERIC_ZERO);
				 		        this.appendCell(row, index.addAndGet(1),getArea(isParking,monthValueVo.get(j).getActualArea()),isParentLevel,NUMERIC_TWO);
				 		        this.appendCell(row, index.addAndGet(1),getAvgPrice(isParking,monthValueVo.get(j).getActualAveragePrice()),isParentLevel,NUMERIC_ZERO);
				 		        this.appendCell(row, index.addAndGet(1),getPrice(isParking,monthValueVo.get(j).getActualPrice()),isParentLevel,NUMERIC_ZERO);
				 		        this.appendCell(row, index.addAndGet(1),monthValueVo.get(j).getAchievingRate(),isParentLevel,ACHIEVING_RATE);
		        			}else {
		        				this.appendCell(row, index.addAndGet(1),handleNum(monthValueVo.get(j).getNum()),isParentLevel,NUMERIC_ZERO);
				 		        this.appendCell(row, index.addAndGet(1),getArea(isParking,monthValueVo.get(j).getSaleArea()),isParentLevel,NUMERIC_TWO);
				 		        this.appendCell(row, index.addAndGet(1),monthValueVo.get(j).getAvgPrice(),isParentLevel,NUMERIC_ZERO);
				 		        this.appendCell(row, index.addAndGet(1),monthValueVo.get(j).getPrice(),isParentLevel,NUMERIC_ZERO);
				 		        this.appendCell(row, index.addAndGet(1),handleNum(monthValueVo.get(j).getActualNum()),isParentLevel,NUMERIC_ZERO);
				 		        this.appendCell(row, index.addAndGet(1),getArea(isParking,monthValueVo.get(j).getActualArea()),isParentLevel,NUMERIC_TWO);
				 		        this.appendCell(row, index.addAndGet(1),monthValueVo.get(j).getActualAveragePrice(),isParentLevel,NUMERIC_ZERO);
				 		        this.appendCell(row, index.addAndGet(1),monthValueVo.get(j).getActualPrice(),isParentLevel,NUMERIC_ZERO);
				 		        this.appendCell(row, index.addAndGet(1),monthValueVo.get(j).getAchievingRate(),isParentLevel,ACHIEVING_RATE);
		        			}
			 	        }
		        	}
		        	//年度合计
		        	BaseExcelValueVO yearValueVo = yearVo.get(i).getYearValueVO();
		        	if(yearValueVo != null) {
		        		if(type.equals(OrgTypeEunm.ORG_BUILD.getKey()) || type.equals(OrgTypeEunm.ORG_PRODUCT.getKey())) {
		        			this.appendCell(row, index.addAndGet(1),getNum(isParking,yearValueVo.getNum()),isParentLevel,NUMERIC_ZERO);
			 		        this.appendCell(row, index.addAndGet(1),getArea(isParking,yearValueVo.getSaleArea()),isParentLevel,NUMERIC_TWO);
			 		        this.appendCell(row, index.addAndGet(1),getAvgPrice(isParking,yearValueVo.getAvgPrice()),isParentLevel,NUMERIC_ZERO);
			 		        this.appendCell(row, index.addAndGet(1),getPrice(isParking,yearValueVo.getPrice()),isParentLevel,NUMERIC_ZERO);
			 		        this.appendCell(row, index.addAndGet(1),getNum(isParking,yearValueVo.getActualNum()),isParentLevel,NUMERIC_ZERO);
			 		        this.appendCell(row, index.addAndGet(1),getArea(isParking,yearValueVo.getActualArea()),isParentLevel,NUMERIC_TWO);
			 		        this.appendCell(row, index.addAndGet(1),getAvgPrice(isParking,yearValueVo.getActualAveragePrice()),isParentLevel,NUMERIC_ZERO);
			 		        this.appendCell(row, index.addAndGet(1),getPrice(isParking,yearValueVo.getActualPrice()),isParentLevel,NUMERIC_ZERO);
			 		        this.appendCell(row, index.addAndGet(1),yearValueVo.getAchievingRate(),isParentLevel,ACHIEVING_RATE);
	        			}else {
	        				this.appendCell(row, index.addAndGet(1),handleNum(yearValueVo.getNum()),isParentLevel,NUMERIC_ZERO);
			 		        this.appendCell(row, index.addAndGet(1),getArea(isParking,yearValueVo.getSaleArea()),isParentLevel,NUMERIC_TWO);
			 		        this.appendCell(row, index.addAndGet(1),yearValueVo.getAvgPrice(),isParentLevel,NUMERIC_ZERO);
			 		        this.appendCell(row, index.addAndGet(1),yearValueVo.getPrice(),isParentLevel,NUMERIC_ZERO);
			 		        this.appendCell(row, index.addAndGet(1),handleNum(yearValueVo.getActualNum()),isParentLevel,NUMERIC_ZERO);
			 		        this.appendCell(row, index.addAndGet(1),getArea(isParking,yearValueVo.getActualArea()),isParentLevel,NUMERIC_TWO);
			 		        this.appendCell(row, index.addAndGet(1),yearValueVo.getActualAveragePrice(),isParentLevel,NUMERIC_ZERO);
			 		        this.appendCell(row, index.addAndGet(1),yearValueVo.getActualPrice(),isParentLevel,NUMERIC_ZERO);
			 		        this.appendCell(row, index.addAndGet(1),yearValueVo.getAchievingRate(),isParentLevel,ACHIEVING_RATE);
	        			}
		        	}
		        }
	        }
	        currentDataRowCursor++;
	    }

	    private String getArea(Integer isParking, String saleArea) {
	    	if(isParking(isParking)) {
	    		return "";
	    	}
			return saleArea;
		}

		private String getPrice(Integer isParking, String strVal) {
	    	if(isEmpty(strVal)) {
				return "";
			}
	    	if(strVal.indexOf("/") < 0) {
				if(isEmpty(strVal)) {
					strVal ="";
				}
			}else {
				String t1 = strVal.substring(0, strVal.indexOf("/")).trim();
				String t2 = strVal.substring(strVal.indexOf("/")+1, strVal.lastIndexOf("/")).trim();
				String t3 = strVal.substring(strVal.lastIndexOf("/")+1, strVal.length()).trim();
				
				if(isParking(isParking)) {
					return t3;
				}else {
					return t2;
				}
			}
			return strVal;
		}

		private String getAvgPrice(Integer isParking, String strVal) {
	    	if(isEmpty(strVal)) {
				return "";
			}
	    	if(strVal.indexOf("/") < 0) {
				if(isEmpty(strVal)) {
					strVal ="";
				}
			}else {
				String t1 = strVal.substring(0, strVal.indexOf("/")).trim();
				String t2 = strVal.substring(strVal.indexOf("/")+1, strVal.length()).trim();
				
				if(isParking(isParking)) {
					return t2;
				}else {
					return t1;
				}
			}
			return strVal;
		}

		private String getNum(Integer isParking, String strVal) {
	    	if(isEmpty(strVal)) {
				return "";
			}
			if(strVal.indexOf("/") < 0) {
				if(isEmpty(strVal)) {
					strVal ="";
				}
			}else {
				String t1 = strVal.substring(0, strVal.indexOf("/")).trim();
				String t2 = strVal.substring(strVal.indexOf("/")+1, strVal.length()).trim();
				if(isEmpty(t1)) {
					t1="0";
				}
				if(isEmpty(t2)) {
					t2="0";
				}
				
				if(isParking(isParking)) {
					return EmptyUtils.fmtPriceMicrometer(new BigDecimal(t2), 0);
				}else {
					return EmptyUtils.fmtPriceMicrometer(new BigDecimal(t1), 0);
				}
			}
			return strVal;
		}

		/**
	     * 导出
	     *
	     * @param outputStream
	     * @throws IOException
	     */
	    public void write(OutputStream outputStream) throws IOException {
	        workbook.write(outputStream);
	    }
	    
	    private boolean isParking(Integer isParking) {
	    	return isParking != null && isParking.intValue() == 1;
	    }
	    
	    private String handleIsMergeTable(String value) {
	    	if(StrUtils.isEmpty(value)) {
	    		return "";
	    	}
	    	return value.equals("0") ? "否" : "是";
	    }
	    
	    private String handlePendingHomeSale(String value) {
	    	if(StrUtils.isEmpty(value)) {
	    		return "";
	    	}
	    	return value.equals("0") ? "非现房销售" : "现房销售";
	    }
	    
	    public static String handleNum(String strVal) {
			if(isEmpty(strVal)) {
				return "";
			}
			if(strVal.indexOf("/") < 0) {
				if(isEmpty(strVal)) {
					strVal ="";
				}
			}else {
				String t1 = strVal.substring(0, strVal.indexOf("/")).trim();
				String t2 = strVal.substring(strVal.indexOf("/")+1, strVal.length()).trim();
				if(isEmpty(t1)) {
					t1="0";
				}
				if(isEmpty(t2)) {
					t2="0";
				}
				return EmptyUtils.fmtPriceMicrometer(new BigDecimal(t1), 0) +" / "+EmptyUtils.fmtPriceMicrometer(new BigDecimal(t2), 0);
			}
			return strVal;
		}
	    
	    public static boolean isEmpty(final String s) {
			if (s == null) {
				return true;
			}
			return (s.isEmpty() || s.trim().length() == 0) || "null".equals(s);
		}
}
